﻿Imports Infragistics.Win.UltraWinGrid
Imports Infragistics.Win
Imports Infragistics.Win.UltraWinEditors
Imports DevExpress.Utils
Imports CrystalClientManagement.BLL.cProjectManagement
Imports Crystal.CLL
Imports CrystalClientManagement.BLL
Imports Infragistics.Win.UltraWinGrid.ExcelExport
Imports Infragistics.Documents.Excel
Imports System.Runtime.InteropServices

Public Class frmProjectManagement

#Region "Declare"
    Private _NullField As String = "--------"
    Private _ClientName As String = "Client Name"
    Private _OnLoad As Boolean = False
    Private _SearchField As String = String.Empty
    Private _SearchValue As String = String.Empty
    Private _AllSearch As Boolean = False
    Private _IsSortColumn As Boolean = False

#End Region

#Region "Form Load"

    Private Sub frmProjectManagement_FormClosing(ByVal sender As Object, ByVal e As System.Windows.Forms.FormClosingEventArgs) Handles Me.FormClosing
        If IsChildFormOpened() = False Then
            e.Cancel = True
        End If
    End Sub

    Private Sub frmProjectManagement_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        LoadFunctionality()
        cmbSearchField.Appearance.ForeColor = Color.Black
    End Sub

#End Region

#Region "Methods"

    Private Sub LoadFunctionality()
        Dim Logmessage As New System.Text.StringBuilder
        Try
            Me.Text = TitleManager.GetTitle("ProjectManagement")
            txtSearchIn.Text = MessageManager.GetMessage("DEFAULTSEARCHTEXT")
            ShowDefaultSelectedColumns()
            DefaultDisableCheckedColumns()
            cmbSearchField.DataSource = LoadSearchField()
            rdAll.Checked = True
            cmbSearchField.Text = _ClientName
            grdProjects.ContextMenu = New ContextMenu()
            Me.grdProjects.DisplayLayout.Override.CellClickAction = UltraWinGrid.CellClickAction.CellSelect
            DisableButtonEditDelete(False)
        Catch ex As Exception
            Throw ex
            Logmessage.Append("There is error in LoadFunctionality of form ProjectManagement.")
            Logmessage.Append(Environment.NewLine)
            Logmessage.Append("Error Message - ")
            Logmessage.Append(ex.ToString())
            Me.LogErrorToDatabase(Logmessage.ToString)
        Finally
            Logmessage = Nothing
        End Try
    End Sub

    Public Sub ShowDefaultSelectedColumns()
        Dim Logmessage As New System.Text.StringBuilder
        Try
            chkClientName.Checked = True
            chkSDD.Checked = True
            chkFDRD.Checked = True
            chkProjectStatus.Checked = True
            chkCompletedDate.Checked = True
            chkDDForm.Checked = True
            chkDTCList.Checked = True
            chkShareRegister.Checked = True
            chkOfferingLists.Checked = True
        Catch ex As Exception
            Throw ex
            Logmessage.Append("There is error in ShowDefaultSelectedColumns of form ProjectManagement.")
            Logmessage.Append(Environment.NewLine)
            Logmessage.Append("Error Message - ")
            Logmessage.Append(ex.ToString())
            Me.LogErrorToDatabase(Logmessage.ToString)
        Finally
            Logmessage = Nothing
        End Try
    End Sub

    Public Sub DefaultDisableCheckedColumns()
        Dim Logmessage As New System.Text.StringBuilder
        Try
            chkClientName.Enabled = False
            chkClientName.Checked = True
            chkClientName.ForeColor = Color.White
            chkSDD.Enabled = False
            chkSDD.Checked = True
            chkSDD.ForeColor = Color.White
        Catch ex As Exception
            Throw ex
            Logmessage.Append("There is error in ShowDefaultSelectedColumns of form ProjectManagement.")
            Logmessage.Append(Environment.NewLine)
            Logmessage.Append("Error Message - ")
            Logmessage.Append(ex.ToString())
            Me.LogErrorToDatabase(Logmessage.ToString)
        Finally
            Logmessage = Nothing
        End Try
    End Sub

    Private Function LoadSearchField() As List(Of String)
        Dim Logmessage As New System.Text.StringBuilder
        Dim lField As New List(Of String)

        Try
            For Each ctrl As Control In CType(pnlCBContainer.Controls(0), Misc.UltraPanelClientArea).Controls
                If ctrl.[GetType]().Equals(GetType(UltraCheckEditor)) Then
                    If DirectCast(ctrl, UltraCheckEditor).Checked Then
                        lField.Add(ctrl.Text)
                    End If
                End If
            Next
            lField.Add(_NullField)
            For Each ctrl As Control In CType(pnlCBContainer.Controls(0), Misc.UltraPanelClientArea).Controls
                If (ctrl.[GetType]().Equals(GetType(UltraCheckEditor))) Then
                    If Not DirectCast(ctrl, UltraCheckEditor).Checked Then
                        lField.Add(ctrl.Text)
                    End If
                End If
            Next
            Return lField
        Catch ex As Exception
            Throw ex
            Logmessage.Append("There is error in Projectsearch of form ProjectManagement.")
            Logmessage.Append(Environment.NewLine)
            Logmessage.Append("Error Message - ")
            Logmessage.Append(ex.ToString())
            Me.LogErrorToDatabase(Logmessage.ToString)
        Finally
            lField = Nothing
            Logmessage = Nothing
        End Try
    End Function

    Private Sub DisableButtonEditDelete(ByVal bool As Boolean)
        btnEdit.Enabled = bool
        btnDelete.Enabled = bool
        btnExport.Enabled = bool
    End Sub

    Public Sub ShowCheckedGridColumns()
        Dim Logmessage As New System.Text.StringBuilder
        Try
            For Each ctrl As Control In CType(pnlCBContainer.Controls(0), Misc.UltraPanelClientArea).Controls
                If DirectCast(ctrl, UltraCheckEditor).Checked Then
                    With grdProjects
                        If .DisplayLayout.Bands(0).Columns.Exists(ctrl.Text) Then
                            .DisplayLayout.Bands(0).Columns(ctrl.Text).Hidden = False
                        End If
                    End With
                Else
                    With grdProjects
                        If .DisplayLayout.Bands(0).Columns.Exists(ctrl.Text) Then
                            .DisplayLayout.Bands(0).Columns(ctrl.Text).Hidden = True
                        End If
                    End With
                End If
            Next
            If (grdProjects.Rows.Count > 0) Then
                grdProjects.DisplayLayout.Bands(0).Columns("Client Name").PerformAutoResize(grdProjects.Rows.Count)
            End If
        Catch ex As Exception
            Throw ex
            Logmessage.Append("There is error in Projectsearch of form ProjectManagement.")
            Logmessage.Append(Environment.NewLine)
            Logmessage.Append("Error Message - ")
            Logmessage.Append(ex.ToString())
            Me.LogErrorToDatabase(Logmessage.ToString)
        Finally
            Logmessage = Nothing
        End Try
    End Sub

    Private Sub SetSearchText()
        If txtSearchIn.Text = String.Empty Then
            txtSearchIn.Text = MessageManager.GetMessage("DEFAULTSEARCHTEXT").Trim
        End If
    End Sub

    Private Function GetRowCount(ByVal Projects As DataTable) As Integer
        Dim Logmessage As New System.Text.StringBuilder

        Dim view As New DataView(Projects)
        Dim distinctValues As DataTable = view.ToTable(True, "ProjectID")
        Try
            Dim count As Integer = distinctValues.Rows.Count
            Return count
        Catch ex As Exception
            Throw ex
            Logmessage.Append("There is error in Projectsearch of form ProjectManagement.")
            Logmessage.Append(Environment.NewLine)
            Logmessage.Append("Error Message - ")
            Logmessage.Append(ex.ToString())
            Me.LogErrorToDatabase(Logmessage.ToString)
        Finally
            view = Nothing
            distinctValues = Nothing
            Logmessage = Nothing

        End Try
    End Function

    Private Sub LoadProjects()
        Dim Logmessage As New System.Text.StringBuilder
        Dim Searchin As Integer
        Dim LookFor As String = ""
        Dim Projects As New DataTable
        Dim IDR As IDataReader = Nothing
        Dim Criteria As Short
        Dim waitDialog As New WaitDialogForm()
        Dim count As Integer = 0
        Try
            lblMessage.Visible = False
            Application.DoEvents()
            _SearchField = cmbSearchField.Text
            If rdContains.Checked Then
                Criteria = SearchCriteria.Contains
            Else
                Criteria = SearchCriteria.BeginWith
            End If
            '*************************************************************
            Select Case cmbSearchField.Text.ToLower
                Case "client name"
                    Searchin = ProjectMenu.ClientName
                Case "listing"
                    Searchin = ProjectMenu.Listing
                Case "ratio"
                    Searchin = ProjectMenu.Ratio
                Case "isin"
                    Searchin = ProjectMenu.ISIO
                Case "sedol"
                    Searchin = ProjectMenu.SEDOL
                Case "cusip"
                    Searchin = ProjectMenu.CUSIP
                Case "sdd"
                    Searchin = ProjectMenu.SDD
                Case "fdrd"
                    Searchin = ProjectMenu.FDRD
                Case "project status"
                    Searchin = ProjectMenu.ProjectStatus
                Case "completed date"
                    Searchin = ProjectMenu.CompletedDate
                Case "ignored"
                    Searchin = ProjectMenu.Ignored
                Case "shares in issue"
                    Searchin = ProjectMenu.ShareinIssue
                Case "shares outstanding"
                    Searchin = ProjectMenu.ShareOutstanding
                Case "free - float"
                    Searchin = ProjectMenu.FreeFloat
                Case "country"
                    Searchin = ProjectMenu.Country
                Case "region"
                    Searchin = ProjectMenu.Region
                Case "report history"
                    Searchin = ProjectMenu.ReportHistory
                Case "dd form"
                    Searchin = ProjectMenu.DDForm
                Case "dtc list"
                    Searchin = ProjectMenu.DTCList
                Case "share register"
                    Searchin = ProjectMenu.ShareRegister
                Case "offerings list"
                    Searchin = ProjectMenu.OfferingList
            End Select
            If _AllSearch Then
                rdAll.Checked = True
                LookFor = String.Empty
                _AllSearch = False
            Else
                LookFor = txtSearchIn.Text.Trim
                rdSearchResult.Checked = True
            End If

            If rdAll.Checked And LookFor = String.Empty Then
                Searchin = ProjectMenu.ClientName
            End If
            Application.DoEvents()
            pnlButton.Enabled = False
            waitDialog.Show()
            IDR = SearchProjects(Searchin, LookFor.Replace("'", "''").Replace("_", "[_]").Replace("%", "[%]"), Criteria)
            Projects.Load(IDR)
            BaseClass.ChangeDataReaderDataTable(Projects)

            If CommonCLL.CheckEmptyDataTable(Projects) Then
                grdProjects.DataSource = Nothing
                waitDialog.Close()
                ShowMessageInfo(MessageManager.GetMessage("NORECORD"))
                lblTotalProjects.Text = "Total Projects:- 0"
                DisableButtonEditDelete(False)
                Exit Sub
            Else
                grdProjects.DataSource = Projects
                lblTotalProjects.Text = "Total Projects:- " & GetRowCount(Projects)
                DefaultDisableCheckedColumns()
                ShowCheckedGridColumns()
                DisableButtonEditDelete(True)
                If Not chkListing.Checked And Not chkRatio.Checked And Not chkIsin.Checked And Not chkCusip.Checked And Not chkSedol.Checked Then
                    HideRows(True)
                End If

            End If
            HideDefaultColumn()
            Me.grdProjects.DisplayLayout.Override.CellClickAction = Infragistics.Win.UltraWinGrid.CellClickAction.CellSelect
            'Me.grdProjects.DisplayLayout.Override.row
        Catch ex As Exception
            ShowMessageInfo(ex.Message)
            Logmessage.Append("There is error in LoadClient of form Management.")
            Logmessage.Append(Environment.NewLine)
            Logmessage.Append("Error Message - ")
            Logmessage.Append(ex.ToString())
            Me.LogErrorToDatabase(Logmessage.ToString)
        Finally
            Projects = Nothing
            IDR = Nothing
            lblTotalProjects.Visible = True
            waitDialog.Close()
            Logmessage = Nothing
            pnlButton.Enabled = True
        End Try
    End Sub
    Private Sub HideRows(ByVal IsTrue As Boolean)
        If grdProjects Is Nothing Then Exit Sub
        If grdProjects.Rows Is Nothing Then Exit Sub
        If grdProjects.Rows.Count = 0 Then Exit Sub
        Dim row As UltraGridRow
        Dim ChildrowCount As Integer = 0
        For Each row In grdProjects.Rows
            'If TypeOf row Is UltraGridGroupByRow Then
            '    Dim groupByRow As UltraGridGroupByRow = DirectCast(row, UltraGridGroupByRow)
            'End If
            If Not Nothing Is row.ChildBands Then
                ChildrowCount = ChildrowCount + 1
                ' Loop throgh each of the child bands.
                Dim childBand As UltraGridChildBand = Nothing
                For Each childBand In row.ChildBands
                    For i As Integer = 0 To childBand.Rows.Count
                        If i < childBand.Rows.Count - 1 Then
                            If childBand.Rows(i).Cells IsNot Nothing AndAlso childBand.Rows(i + 1).Cells IsNot Nothing Then
                                If childBand.Rows(i).Cells(0).Value.ToString = childBand.Rows(i + 1).Cells(0).Value.ToString Then
                                    childBand.Rows(i + 1).Hidden = IsTrue
                                End If
                            End If
                        End If
                    Next
                    ' Call this method recursivedly for each child rows collection.
                Next
            End If

        Next
        If ChildrowCount = 0 Then
            'For Each row In grdProjects.Rows
            For i As Integer = 0 To grdProjects.Rows.Count
                If i < grdProjects.Rows.Count - 1 Then
                    If grdProjects.Rows(i).Cells(0).Value.ToString = grdProjects.Rows(i + 1).Cells(0).Value.ToString Then
                        grdProjects.Rows(i + 1).Hidden = IsTrue
                    End If
                End If

            Next
            'Next
        End If
    End Sub
    'Private Sub ChangeCaptionGridHeader()
    '    With grdProjects.DisplayLayout.Bands(0)
    '        .Columns("ClientName").Header.Caption = chkClientName.Text
    '        .Columns("ListingName").Header.Caption = chkListing.Text
    '        .Columns("Ratio").Header.Caption = chkRatio.Text
    '        .Columns("Sedol").Header.Caption = chkSedol.Text
    '        .Columns("Cusip").Header.Caption = chkCusip.Text
    '        .Columns("DeliveryDate").Header.Caption = chkSDD.Text
    '        .Columns("FinalDiscRequestDate").Header.Caption = chkFDRD.Text
    '        .Columns("ProjectStatus").Header.Caption = chkProjectStatus.Text
    '        .Columns("CompletionDate").Header.Caption = chkCompletedDate.Text
    '        .Columns("IsIgnored").Header.Caption = chkIgnored.Text
    '        .Columns("ShareInIssue").Header.Caption = chkShareInIssue.Text
    '        .Columns("ShareOutStanding").Header.Caption = chkShareOutstanding.Text
    '        .Columns("FreeFloatValue").Header.Caption = chkFreeFloat.Text
    '        .Columns("CountryName").Header.Caption = chkCountry.Text
    '        .Columns("Region").Header.Caption = chkRegion.Text
    '        .Columns("ReportHistory").Header.Caption = chkReportHistory.Text
    '        .Columns("HasDDForm").Header.Caption = chkDDForm.Text
    '        .Columns("HasDTCList").Header.Caption = chkDTCList.Text
    '        .Columns("HasShareRegister").Header.Caption = chkShareRegister.Text
    '        .Columns("HasOfferingList").Header.Caption = chkOfferingLists.Text
    '    End With
    'End Sub

    Private Sub HideDefaultColumn()
        grdProjects.DisplayLayout.Rows.Band.Columns("ProjectID").Hidden = True
    End Sub

    Private Sub MultiRowSelection(ByVal isTrue As Boolean)
        btnEdit.Enabled = isTrue
        btnExeSummary.Enabled = isTrue
        btnDelete.Enabled = isTrue
    End Sub

    Private Sub ShowMessageInfo(ByVal message As String)
        MessageBox.Show(message, BaseClass.crystalCaption, MessageBoxButtons.OK, MessageBoxIcon.Information)
    End Sub

    Private Function IsChildFormOpened() As Boolean
        For lmFomrs As Integer = 0 To Application.OpenForms.Count - 1
            If Application.OpenForms(lmFomrs).Name = "frmAddEditProject" Then
                Application.OpenForms(lmFomrs).BringToFront()
                Return False
            End If
        Next
        Return True
    End Function

    'Private Sub EditProject()
    '    Dim Logmessage As New System.Text.StringBuilder
    '    Dim isAlreadyOpen As Boolean = False
    '    Try
    '        If grdProjects.ActiveRow Is Nothing Then Exit Sub
    '        Dim formText As String = "Crystal - Edit " + grdProjects.ActiveRow.Cells("Client Name").Value
    '        For lmFomrs As Integer = 0 To Application.OpenForms.Count - 1
    '            If Application.OpenForms(lmFomrs).Name = "frmAddEditProject" Then
    '                If CType(Application.OpenForms(lmFomrs), frmAddEditProject).WindowState = FormWindowState.Minimized Then
    '                    CType(Application.OpenForms(lmFomrs), frmAddEditProject).WindowState = FormWindowState.Normal
    '                Else
    '                    Application.OpenForms(lmFomrs).BringToFront()
    '                End If
    '                FocusOnExistingInstance(formText, "frmAddEditProject", CType(Application.OpenForms(lmFomrs), frmAddEditProject).WindowState, isAlreadyOpen)
    '                If isAlreadyOpen Then
    '                    Return
    '                End If
    '            End If
    '        Next
    '        Dim frmEditClient As New frmAddEditProject
    '        'frmEditClient.clientID = grdProjects.ActiveRow.Cells("ClientID").Value
    '        frmEditClient.Text = formText
    '        frmEditClient.StartPosition = FormStartPosition.CenterScreen
    '        'AddHandler frmEditClient.AddParticularClientInGrid, AddressOf AddParticularClientInGrid
    '        frmEditClient.Show()
    '    Catch ex As Exception
    '        ShowMessageInfo(ex.Message)
    '        Logmessage.Append("There is error in GetInvestorByFundID of form findmanagement.")
    '        Logmessage.Append(Environment.NewLine)
    '        Logmessage.Append("Error Message - ")
    '        Logmessage.Append(ex.ToString())
    '        Me.LogErrorToDatabase(Logmessage.ToString)
    '    Finally
    '        isAlreadyOpen = False
    '    End Try
    'End Sub

    Private Function CreateDatatable() As DataTable
        Dim Logmessage As New System.Text.StringBuilder

        Dim dt As DataTable = Nothing
        Dim dr As DataRow = Nothing
        Try
            dt = New DataTable
            dt.Columns.Add("ProjectID", GetType(System.String))
            dt.Columns.Add("Client Name", GetType(System.String))
            dt.Columns.Add("Listing", GetType(System.String))
            dt.Columns.Add("Ratio", GetType(System.String))
            dt.Columns.Add("ISIN", GetType(System.String))
            dt.Columns.Add("SEDOL", GetType(System.String))
            dt.Columns.Add("CUSIP", GetType(System.String))
            dt.Columns.Add("SDD", GetType(System.String))
            dt.Columns.Add("FDRD", GetType(System.String))
            dt.Columns.Add("Project Status", GetType(System.String))
            dt.Columns.Add("Completed Date", GetType(System.String))
            dt.Columns.Add("Ignored", GetType(System.String))
            dt.Columns.Add("Shares in Issue", GetType(System.String))
            dt.Columns.Add("Shares Outstanding", GetType(System.String))
            dt.Columns.Add("Free - Float", GetType(System.String))
            dt.Columns.Add("Country", GetType(System.String))
            dt.Columns.Add("Region", GetType(System.String))
            dt.Columns.Add("Report History", GetType(System.String))
            dt.Columns.Add("DD Form", GetType(System.String))
            dt.Columns.Add("DTC List", GetType(System.String))
            dt.Columns.Add("Share Register", GetType(System.String))
            dt.Columns.Add("Offerings List", GetType(System.String))
            Return dt
        Catch ex As Exception
            If (Not Me.IsDisposed) Then
                ShowMessageInfo(ex.Message)
            End If
            ShowMessageInfo(ex.Message)
            Logmessage.Append("There is error in GetInvestorByFundID of form findmanagement.")
            Logmessage.Append(Environment.NewLine)
            Logmessage.Append("Error Message - ")
            Logmessage.Append(ex.ToString())
            Me.LogErrorToDatabase(Logmessage.ToString)
            Return Nothing
        Finally
            dt = Nothing
            Logmessage = Nothing

        End Try
    End Function

    Public Sub AddParticularProjectInGrid(ByVal ProjectID As Integer)
        Dim Logmessage As New System.Text.StringBuilder
        Try
            If rdAll.Checked Then
                rdAll.PerformClick()
            Else
                LoadProjects()
            End If
            'If (Me.IsDisposed) Then Exit Sub
            'Dim dDtFund As New DataTable
            'Dim dDtgrdProjects As DataTable
            'Dim Dr() As DataRow
            'Dim dv As New DataView
            'Dim dataSet As New DataSet
            'Dim Drow As DataRow = Nothing
            '    dataSet = GetParticularProject(ProjectID)
            '    dDtFund = dataSet.Tables(0)
            '    If dDtFund.Rows.Count = 0 Then Exit Sub
            '    dDtgrdProjects = CType(grdProjects.DataSource, DataTable)
            '    If dDtgrdProjects Is Nothing Then
            '        dDtgrdProjects = CreateDatatable()
            '        For i As Integer = 0 To dDtFund.Rows.Count - 1
            '            Drow = dDtgrdProjects.NewRow
            '            Drow("ProjectID") = dDtFund.Rows(i)("ProjectID")
            '            Drow("Client Name") = dDtFund.Rows(i)("Client Name")
            '            Drow("Listing") = dDtFund.Rows(i)("Listing")
            '            Drow("Ratio") = dDtFund.Rows(i)("Ratio")
            '            Drow("ISIN") = dDtFund.Rows(i)("ISIN")
            '            Drow("SEDOL") = dDtFund.Rows(i)("SEDOL")
            '            Drow("CUSIP") = dDtFund.Rows(i)("CUSIP")
            '            Drow("SDD") = dDtFund.Rows(i)("SDD")
            '            Drow("FDRD") = dDtFund.Rows(i)("FDRD")
            '            Drow("Project Status") = dDtFund.Rows(i)("Project Status")
            '            Drow("Completed Date") = dDtFund.Rows(i)("Completed Date")
            '            Drow("Ignored") = dDtFund.Rows(i)("Ignored")
            '            Drow("Shares In Issue") = dDtFund.Rows(i)("Shares In Issue")
            '            Drow("Shares Outstanding") = dDtFund.Rows(i)("Shares Outstanding")
            '            Drow("Free - Float") = dDtFund.Rows(i)("Free - Float")
            '            Drow("Country") = dDtFund.Rows(i)("Country")
            '            Drow("Region") = dDtFund.Rows(i)("Region")
            '            Drow("Report History") = dDtFund.Rows(i)("Report History")
            '            Drow("DD Form") = dDtFund.Rows(i)("DD Form")
            '            Drow("DTC List") = dDtFund.Rows(i)("DTC List")
            '            Drow("Share Register") = dDtFund.Rows(i)("Share Register")
            '            Drow("Offerings List") = dDtFund.Rows(i)("Offerings List")
            '            dDtgrdProjects.Rows.Add(Drow)
            '        Next

            '        dv = New DataView(dDtgrdProjects)
            '        dv.Sort = "[Client Name] asc"
            '        grdProjects.DataSource = dDtgrdProjects
            '        grdProjects.DisplayLayout.Bands(0).Columns("Client Name").PerformAutoResize(grdProjects.Rows.Count)
            '        HideDefaultColumn()

            '    Else
            '        Dr = dDtgrdProjects.Select("ProjectID=" + ProjectID.ToString())
            '        For Each row As DataRow In Dr
            '            dDtgrdProjects.Rows.Remove(row)
            '        Next
            '        dDtgrdProjects.Merge(dDtFund, True, MissingSchemaAction.Ignore)
            '        grdProjects.DataSource = dDtgrdProjects
            '        grdProjects.DisplayLayout.Bands(0).Columns("Client Name").PerformAutoResize(grdProjects.Rows.Count)
            '        If Not chkListing.Checked And Not chkRatio.Checked And Not chkIsin.Checked And Not chkCusip.Checked And Not chkSedol.Checked Then
            '            HideRows(True)
            '        End If
            '        Exit Sub
            '        'End If
            '        dv = New DataView(dDtgrdProjects)
            '        dv.Sort = "[Client Name] asc"
            '        grdProjects.DataSource = dv.ToTable()
            '        grdProjects.DisplayLayout.Bands(0).Columns("Client Name").PerformAutoResize(grdProjects.Rows.Count)
            '    End If
            '    ShowCheckedGridColumns()
            '    grdProjects.DisplayLayout.Bands(0).Columns("Client Name").PerformAutoResize(grdProjects.Rows.Count)
            '    If Not chkListing.Checked And Not chkRatio.Checked And Not chkIsin.Checked And Not chkCusip.Checked And Not chkSedol.Checked Then
            '        HideRows(True)
            '    End If
        Catch ex As Exception
            If (Not Me.IsDisposed) Then
                ShowMessageInfo(ex.Message)
                Logmessage.Append("There is error in GetInvestorByFundID of form findmanagement.")
                Logmessage.Append(Environment.NewLine)
                Logmessage.Append("Error Message - ")
                Logmessage.Append(ex.ToString())
                Me.LogErrorToDatabase(Logmessage.ToString)
            End If
        Finally
            If grdProjects.Rows.Count > 0 Then
                DisableButtonEditDelete(True)
                'Dim colRow As UltraGridRow = Me.grdProjects.Rows.OfType(Of UltraGridRow)().ToList().Find(Function(row) row.Cells("ProjectID").Text.Equals(ProjectID.ToString(), StringComparison.CurrentCultureIgnoreCase))
                'grdProjects.ActiveRow = colRow
            Else
                DisableButtonEditDelete(False)
            End If
            'Logmessage = Nothing
            'dDtFund = Nothing
            'dDtgrdProjects = Nothing
            'Dr = Nothing
            Dim str As String = GetRowCount(CType(grdProjects.DataSource, DataTable)).ToString
            lblTotalProjects.Text = "Total Projects:- " + str
        End Try
    End Sub

    Private Sub CheckSelectedField(ByVal text As String)
        For Each ctrl As Control In CType(pnlCBContainer.Controls(0), Misc.UltraPanelClientArea).Controls
            If DirectCast(ctrl, UltraCheckEditor).Text = text Then
                DirectCast(ctrl, UltraCheckEditor).Checked = True
            End If

        Next
    End Sub

    Private Sub SetTextValueOnChange()
        If (txtSearchIn.Text = MessageManager.GetMessage("DEFAULTSEARCHTEXT") Or txtSearchIn.Text = String.Empty) And (cmbSearchField.Text = "Completed Date" _
                                                                                                                       Or cmbSearchField.Text = "SDD" Or cmbSearchField.Text = "FDRD") Then
            txtSearchIn.Text = MessageManager.GetMessage("DEFAULTDATEFORMAT")
        End If
    End Sub

    Private Sub CheckBoxChangeSearchField()
        _SearchField = cmbSearchField.Text
        If txtSearchIn.Text.Trim <> MessageManager.GetMessage("DEFAULTSEARCHTEXT").Trim Or txtSearchIn.Text.Trim <> MessageManager.GetMessage("DEFAULTDATEFORMAT").Trim Then
            _SearchValue = txtSearchIn.Text
        End If
        cmbSearchField.DataSource = LoadSearchField()
        cmbSearchField.Text = _SearchField
        If _SearchValue.Length > 0 Then
            txtSearchIn.Text = _SearchValue
        End If
    End Sub

    Private Sub ShowColumnOnChecked(ByVal uce As UltraCheckEditor)
        If grdProjects.Rows.Count > 0 Then
            If Not grdProjects.DisplayLayout.Bands(0).Columns.Exists(uce.Text) Then Exit Sub
            If uce.Checked Then
                grdProjects.DisplayLayout.Bands(0).Columns(uce.Text).Hidden = False
            Else
                grdProjects.DisplayLayout.Bands(0).Columns(uce.Text).Hidden = True
            End If
        End If
    End Sub

    Private Sub ShowMessageInfos(ByVal Message As String)
        MessageBox.Show(Message, CommonCLL.mstrMsgBoxCaption, MessageBoxButtons.OK, MessageBoxIcon.Information)
    End Sub
#End Region

#Region "Events"

    Private Sub cmbSearchField_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles cmbSearchField.SelectionChanged
        If cmbSearchField.Text = _NullField Then
            ShowMessageInfo(MessageManager.GetMessage("INVALIDSEARCHFIELD").Trim)
            cmbSearchField.Text = _ClientName
            Return
        Else
            CheckSelectedField(cmbSearchField.Value)
            SetTextValueOnChange()
        End If
    End Sub

    Private Sub rdAll_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles rdAll.Click
        If rdAll.Checked Then
            _AllSearch = True
            LoadProjects()
        End If
    End Sub

    Private Sub txtSearchIn_Enter(ByVal sender As Object, ByVal e As System.EventArgs) Handles txtSearchIn.Enter
        If txtSearchIn.Text.Trim = MessageManager.GetMessage("DEFAULTSEARCHTEXT").Trim Or txtSearchIn.Text.Trim = MessageManager.GetMessage("DEFAULTDATEFORMAT").Trim Then
            txtSearchIn.Text = String.Empty
        End If
    End Sub

    Private Sub txtSearchIn_KeyDown(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles txtSearchIn.KeyDown
        If e.KeyCode = Keys.Enter Then
            If txtSearchIn.Text = String.Empty Then
                _AllSearch = True
            End If
            LoadProjects()
            If txtSearchIn.Text.Length > 0 Then
                rdSearchResult.Checked = True
            End If
        End If
    End Sub

    Private Sub grdProjects_BeforeSortChange(ByVal sender As Object, ByVal e As Infragistics.Win.UltraWinGrid.BeforeSortChangeEventArgs) Handles grdProjects.BeforeSortChange
        If _IsSortColumn Then
            e.Cancel = True
            _IsSortColumn = False
        End If
    End Sub
    Private Function CheckSecondRowVisible() As Boolean
        If chkListing.Checked Or chkRatio.Checked Or chkIsin.Checked Or chkCusip.Checked Or chkSedol.Checked Then
            Return True
        End If
        Return False
    End Function
    Private Sub grdProjects_InitializeLayout(ByVal sender As Object, ByVal e As Infragistics.Win.UltraWinGrid.InitializeLayoutEventArgs) Handles grdProjects.InitializeLayout
        e.Layout.Override.MergedCellStyle = MergedCellStyle.Always
        ' MergedCellEvaluator property can be used to speficy custom logic for merging cells.
        For i As Integer = 1 To e.Layout.Bands(0).Columns.Count - 1
            e.Layout.Bands(0).Columns(i).MergedCellEvaluator = New CustomMergedCellEvaluators()
        Next
        e.Layout.ViewStyleBand = ViewStyleBand.OutlookGroupBy
        e.Layout.Bands(0).SortedColumns.Add("SDD", False, True)
        e.Layout.Rows.ExpandAll(False)

    End Sub

    Private Sub grdProjects_MouseDown(ByVal sender As Object, ByVal e As System.Windows.Forms.MouseEventArgs) Handles grdProjects.MouseDown
        Dim mouseupUIElement As UIElement _
            = grdProjects.DisplayLayout.UIElement.ElementFromPoint( _
            New Point(e.X, e.Y))

        ' retrieve the Column from the UIElement
        Dim mouseupColumn As UltraGridColumn _
        = mouseupUIElement.GetContext(GetType(UltraGridColumn))

        ' if there is a column object reference, display column key
        If Not mouseupColumn Is Nothing Then
            If mouseupColumn.Key = "ISIN" Or mouseupColumn.Key = "Listing" Or _
                mouseupColumn.Key = "Ratio" Or mouseupColumn.Key = "CUSIP" Or mouseupColumn.Key = "SEDOL" Then
                _IsSortColumn = True
            End If
        End If
        Dim index As Integer = -1
    End Sub

    Private Sub btnDelete_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnDelete.Click
        If grdProjects.ActiveRow Is Nothing Then Exit Sub
        If grdProjects.ActiveRow.Cells Is Nothing Then Exit Sub
        Dim dDtgrdClients As DataTable
        Dim Dr() As DataRow
        Try
            If grdProjects.Rows.Count = 0 Then
                Exit Sub
            End If
            If (grdProjects.ActiveRow Is Nothing) Then
                ShowMessageInfo(MessageManager.GetMessage("SELECTTHERECORD"))
                Exit Sub
            Else
                grdProjects.Selected.Rows.Add(grdProjects.ActiveRow)
            End If
            Dim Status As String = grdProjects.ActiveRow.Cells("Project Status").Value.ToString
            Dim ProjectID As Integer = grdProjects.ActiveRow.Cells("ProjectID").Value
            If Status <> String.Empty And ProjectID <> 0 Then
                If grdProjects.ActiveRow.Cells("Project Status").Value.ToString = "Cancelled" Then
                    ShowMessageInfos(MessageManager.GetMessage("PROJECTALREADYCANCLD"))
                ElseIf grdProjects.ActiveRow.Cells("Project Status").Value.ToString = "Completed" Then
                    If MessageBox.Show(MessageManager.GetMessage("PRJ_NT_DLT_BUT_IGNR"), BaseClass.crystalCaption, MessageBoxButtons.YesNo, MessageBoxIcon.Information) = Windows.Forms.DialogResult.Yes Then
                        Status = cProjectManagement.DeleteUpdateProjectStatus(ProjectID)
                        If Status <> String.Empty And Status = "Updated" Then
                            ShowMessageInfos(MessageManager.GetMessage("PROJECT_IGNORED"))
                        End If
                    End If
                Else
                    If MessageBox.Show(MessageManager.GetMessage("WANT_DELETE_PROJECT"), BaseClass.crystalCaption, MessageBoxButtons.YesNo, MessageBoxIcon.Information) = Windows.Forms.DialogResult.Yes Then
                        Status = cProjectManagement.DeleteUpdateProjectStatus(ProjectID)
                        If Status <> String.Empty And Status = "Updated" Then
                            dDtgrdClients = CType(grdProjects.DataSource, DataTable)
                            Dr = dDtgrdClients.Select("ProjectID=" + grdProjects.ActiveRow.Cells("ProjectID").Value.ToString())
                            For Each row As DataRow In Dr
                                row("Project Status") = "Cancelled"
                            Next
                            ShowMessageInfos(MessageManager.GetMessage("PROJECT_MRKD_CNCLD"))
                        ElseIf Status <> String.Empty And Status = "Deleted" Then
                            dDtgrdClients = CType(grdProjects.DataSource, DataTable)
                            Dr = dDtgrdClients.Select("ProjectID=" + grdProjects.ActiveRow.Cells("ProjectID").Value.ToString())
                            For Each row As DataRow In Dr
                                dDtgrdClients.Rows.Remove(row)
                            Next
                            ShowMessageInfos(MessageManager.GetMessage("PROJECT_DELETED"))
                        End If
                    End If

                End If
            End If
        Catch ex As Exception
            MessageBox.Show(ex.Message, BaseClass.crystalCaption, MessageBoxButtons.OK, MessageBoxIcon.Error)
        Finally
            dDtgrdClients = Nothing
            Dr = Nothing
        End Try
    End Sub

    Private Sub EditProject()
        If grdProjects.ActiveRow Is Nothing Then Exit Sub
        If grdProjects.Rows.Count = 0 OrElse grdProjects.ActiveRow.Cells Is Nothing Then
            Exit Sub
        End If
        If (grdProjects.ActiveRow Is Nothing) Then
            ShowMessageInfo(MessageManager.GetMessage("SELECTTHERECORD"))
            Exit Sub
        Else
            grdProjects.Selected.Rows.Add(grdProjects.ActiveRow)
        End If
        Dim isAlreadyOpen As Boolean = False
        Dim formText As String = "Crystal - Edit " + grdProjects.ActiveRow.Cells("Client Name").Value
        For lmFomrs As Integer = 0 To Application.OpenForms.Count - 1
            If Application.OpenForms(lmFomrs).Name = "frmAddEditProject" Then
                If CType(Application.OpenForms(lmFomrs), frmAddEditProject).WindowState = FormWindowState.Minimized Then
                    CType(Application.OpenForms(lmFomrs), frmAddEditProject).WindowState = FormWindowState.Normal
                Else
                    Application.OpenForms(lmFomrs).BringToFront()
                End If
                FocusOnExistingInstance(formText, "frmAddEditProject", CType(Application.OpenForms(lmFomrs), frmAddEditProject).WindowState, isAlreadyOpen)
                If isAlreadyOpen Then
                    Return
                End If
            End If
        Next
        Dim ProjectID As Integer = grdProjects.ActiveRow.Cells("ProjectID").Value
        Dim objAddEditProject As New frmAddEditProject
        objAddEditProject.projectID = ProjectID
        objAddEditProject.Text = formText
        objAddEditProject.StartPosition = FormStartPosition.CenterScreen
        AddHandler objAddEditProject.AddParticularProjectInGrid, AddressOf AddParticularProjectInGrid
        objAddEditProject.Show()
    End Sub

    Private Sub btnEdit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnEdit.Click
        EditProject()
    End Sub

    Private Sub grdProjects_DoubleClickRow(ByVal sender As Object, ByVal e As Infragistics.Win.UltraWinGrid.DoubleClickRowEventArgs) Handles grdProjects.DoubleClickRow
        EditProject()
    End Sub
#End Region

#Region "CheckBox Events"

    Private Sub chkClientName_CheckedChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles chkClientName.CheckedChanged
        CheckBoxChangeSearchField()
        ShowColumnOnChecked(chkClientName)
    End Sub

    Private Sub chkCompletedDate_CheckedChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles chkCompletedDate.CheckedChanged
        CheckBoxChangeSearchField()
        ShowColumnOnChecked(chkCompletedDate)
    End Sub

    Private Sub chkCountry_CheckedChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles chkCountry.CheckedChanged
        CheckBoxChangeSearchField()
        ShowColumnOnChecked(chkCountry)
    End Sub

    Private Sub chkCusip_CheckedChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles chkCusip.CheckedChanged
        CheckBoxChangeSearchField()
        ShowColumnOnChecked(chkCusip)
        If Not chkListing.Checked And Not chkRatio.Checked And Not chkIsin.Checked And Not chkCusip.Checked And Not chkSedol.Checked Then
            HideRows(True)
        Else
            HideRows(False)
        End If
    End Sub

    Private Sub chkDDForm_CheckedChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles chkDDForm.CheckedChanged
        CheckBoxChangeSearchField()
        ShowColumnOnChecked(chkDDForm)
    End Sub

    Private Sub chkDTCList_CheckedChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles chkDTCList.CheckedChanged
        CheckBoxChangeSearchField()
        ShowColumnOnChecked(chkDTCList)
    End Sub

    Private Sub chkFDRD_CheckedChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles chkFDRD.CheckedChanged
        CheckBoxChangeSearchField()
        ShowColumnOnChecked(chkFDRD)
    End Sub

    Private Sub chkFreeFloat_CheckedChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles chkFreeFloat.CheckedChanged
        CheckBoxChangeSearchField()
        ShowColumnOnChecked(chkFreeFloat)
    End Sub

    Private Sub chkIgnored_CheckedChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles chkIgnored.CheckedChanged
        CheckBoxChangeSearchField()
        ShowColumnOnChecked(chkIgnored)
    End Sub

    Private Sub chkIsin_CheckedChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles chkIsin.CheckedChanged
        CheckBoxChangeSearchField()
        ShowColumnOnChecked(chkIsin)
        If Not chkListing.Checked And Not chkRatio.Checked And Not chkIsin.Checked And Not chkCusip.Checked And Not chkSedol.Checked Then
            HideRows(True)
        Else
            HideRows(False)
        End If
    End Sub

    Private Sub chkListing_CheckedChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles chkListing.CheckedChanged
        CheckBoxChangeSearchField()
        ShowColumnOnChecked(chkListing)
        If Not chkListing.Checked And Not chkRatio.Checked And Not chkIsin.Checked And Not chkCusip.Checked And Not chkSedol.Checked Then
            HideRows(True)
        Else
            HideRows(False)
        End If
    End Sub

    Private Sub chkOfferingLists_CheckedChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles chkOfferingLists.CheckedChanged
        CheckBoxChangeSearchField()
        ShowColumnOnChecked(chkOfferingLists)
    End Sub

    Private Sub chkProjectStatus_CheckedChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles chkProjectStatus.CheckedChanged
        CheckBoxChangeSearchField()
        ShowColumnOnChecked(chkProjectStatus)
    End Sub

    Private Sub chkRatio_CheckedChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles chkRatio.CheckedChanged
        CheckBoxChangeSearchField()
        ShowColumnOnChecked(chkRatio)
        If Not chkListing.Checked And Not chkRatio.Checked And Not chkIsin.Checked And Not chkCusip.Checked And Not chkSedol.Checked Then
            HideRows(True)
        Else
            HideRows(False)
        End If
    End Sub

    Private Sub chkRegion_CheckedChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles chkRegion.CheckedChanged
        CheckBoxChangeSearchField()
        ShowColumnOnChecked(chkRegion)
    End Sub

    Private Sub chkReportHistory_CheckedChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles chkReportHistory.CheckedChanged
        CheckBoxChangeSearchField()
        ShowColumnOnChecked(chkReportHistory)
    End Sub

    Private Sub chkSDD_CheckedChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles chkSDD.CheckedChanged
        CheckBoxChangeSearchField()
        ShowColumnOnChecked(chkSDD)
        'If chkSDD.Checked Then
        '    For Each row As UltraGridRow In grdProjects.Rows
        '        If row.Hidden Then
        '            row.Hidden = False
        '        End If
        '    Next
        'End If
    End Sub

    Private Sub chkSedol_CheckedChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles chkSedol.CheckedChanged
        CheckBoxChangeSearchField()
        ShowColumnOnChecked(chkSedol)
        If Not chkListing.Checked And Not chkRatio.Checked And Not chkIsin.Checked And Not chkCusip.Checked And Not chkSedol.Checked Then
            HideRows(True)
        Else
            HideRows(False)
        End If
    End Sub

    Private Sub chkShareInIssue_CheckedChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles chkShareInIssue.CheckedChanged
        CheckBoxChangeSearchField()
        ShowColumnOnChecked(chkShareInIssue)
    End Sub

    Private Sub chkShareOutstanding_CheckedChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles chkShareOutstanding.CheckedChanged
        CheckBoxChangeSearchField()
        ShowColumnOnChecked(chkShareOutstanding)
    End Sub

    Private Sub chkShareRegister_CheckedChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles chkShareRegister.CheckedChanged
        CheckBoxChangeSearchField()
        ShowColumnOnChecked(chkShareRegister)
    End Sub

    Private Sub grdProjects_AfterSelectChange(ByVal sender As Object, ByVal e As Infragistics.Win.UltraWinGrid.AfterSelectChangeEventArgs) Handles grdProjects.AfterSelectChange
        If grdProjects.Selected.Rows.Count > 1 Then
            MultiRowSelection(False)
        Else
            MultiRowSelection(True)
        End If
    End Sub

    Private Sub grdProjects_BeforeEnterEditMode(ByVal sender As Object, ByVal e As System.ComponentModel.CancelEventArgs) Handles grdProjects.BeforeEnterEditMode
        grdProjects.ActiveRow.Activation = UltraWinGrid.Activation.ActivateOnly
    End Sub

    Private Sub btnAddClient_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAddClient.Click
        If (CType(sender, Control).Name = "btnAddClient") Then
            For lmFomrs As Integer = 0 To Application.OpenForms.Count - 1
                If Application.OpenForms(lmFomrs).Name = "frmAddEditProject" Then
                    If CType(Application.OpenForms(lmFomrs), frmAddEditProject).WindowState = FormWindowState.Minimized Then
                        CType(Application.OpenForms(lmFomrs), frmAddEditProject).WindowState = FormWindowState.Maximized
                        'Application.OpenForms(lmFomrs).BringToFront()
                        Return
                    End If
                    If (FocusTheExistingInstanceOfForm("", "frmAddEditProject", CType(Application.OpenForms(lmFomrs), frmAddEditProject).WindowState)) Then
                        Exit Sub
                    End If
                End If
            Next
            Dim addEditProject As New frmAddEditProject
            addEditProject.Text = "Crystal - Add Project"
            'addEditClient.txtStreet1.Focus()
            AddHandler addEditProject.AddParticularProjectInGrid, AddressOf AddParticularProjectInGrid
            addEditProject.Show()
            'Return
        End If
    End Sub

    Public Function FocusTheExistingInstanceOfForm(ByVal id As String, ByVal Filename As String, ByVal windowstate As FormWindowState) As Boolean
        Dim objAdd As frmAddEditProject = Nothing
        Dim objClient As frmProjectManagement = Nothing
        Try
            For i As Integer = 0 To My.Application.OpenForms.Count - 1
                Select Case Filename
                    Case "frmAddEditProject"
                        If (My.Application.OpenForms(i).Name = Filename) Then
                            objAdd = My.Application.OpenForms(i)
                            If (windowstate = FormWindowState.Maximized) Then
                                objAdd.WindowState = FormWindowState.Maximized
                            End If
                            If (windowstate = FormWindowState.Normal) Then
                                objAdd.WindowState = FormWindowState.Normal
                            End If
                            objAdd.Focus()
                            Return True
                        End If
                End Select
            Next
        Catch ex As Exception
            MessageBox.Show(ex.Message)
            ''Logging
            Dim MsgLog As New System.Text.StringBuilder
            MsgLog.Append("There is error in FocusTheExistingInstanceOfForm in Client Management.")
            MsgLog.Append(Environment.NewLine)
            MsgLog.Append("Error Details:- ")
            MsgLog.Append(ex.ToString())
            CreateErrorLog(MsgLog)
            MsgLog = Nothing
        Finally
            'objAdd.Dispose()
            'objClient.Dispose()
        End Try

    End Function
#End Region

    Private Sub btnExit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnExit.Click
        Me.Close()
    End Sub

    Private Sub btnExport_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnExport.Click
        If grdProjects Is Nothing Then Exit Sub
        If Not grdProjects.Rows.Count > 0 Then Exit Sub
        Dim Logmessage As New System.Text.StringBuilder
        Dim fileName As String = String.Empty
        Dim dialog As New SaveFileDialog
        Dim showDialog As New DialogResult
        Dim name As String
        Dim ExpToExcel As New UltraGridExcelExporter
        Dim listOfHiddenColumns As New List(Of UltraWinGrid.UltraGridColumn)
        Dim listOfHiddenRows As New List(Of UltraGridRow)
        Dim excel As Microsoft.Office.Interop.Excel.Application
        Dim wb As Microsoft.Office.Interop.Excel.Workbook
        Try
            fileName = "Projects - " + DateTime.Now.ToString()
            fileName = fileName.Replace(":", "_").Replace("/", "_")
            With dialog
                .FileName = fileName
                .InitialDirectory = "C:\\"
                .Filter = "Excel (2007-2010 format)(*.xlsx)|*.xlsx|Excel (97-2003 format)(*.xls)|*.xls"
                .DefaultExt = ".xlsx"
            End With
            showDialog = dialog.ShowDialog()
            If showDialog = DialogResult.OK Then
                Dim strExtension As String = System.IO.Path.GetExtension(dialog.FileName)
                name = dialog.FileName
                Dim selectedformat As WorkbookFormat

                If strExtension = ".xlsx" Then
                    selectedformat = WorkbookFormat.Excel2007
                Else
                    selectedformat = WorkbookFormat.Excel97To2003
                End If
                pnlCBContainer.Enabled = False
                pnlButton.Enabled = False

                Dim ExcWorkbook As New Workbook(selectedformat, WorkbookPaletteMode.CustomPalette)
                Dim lobjws As Worksheet = ExcWorkbook.Worksheets.Add("Project")
                'UltraGridExcelExporter1.Export(grdProjects, lobjws, 0, 0)
                If rdSearchResult.Checked Then
                    ExpToExcel.Export(grdProjects, lobjws, 0, 0)
                Else
                    Dim row1 As UltraWinGrid.UltraGridRow
                    Dim column As UltraWinGrid.UltraGridColumn
                    For Each column In grdProjects.DisplayLayout.Bands(0).Columns
                        If column.Index > 0 Then
                            If column.Hidden = True Then
                                listOfHiddenColumns.Add(column)
                            End If
                            column.Hidden = False
                        End If
                    Next
                    For Each row1 In grdProjects.Rows
                        If row1.Hidden = True Then
                            listOfHiddenRows.Add(row1)
                        End If
                        row1.Hidden = False
                    Next
                    'ExpToExcel.Export(grdClients, lobjws, 0, 0)
                    If listOfHiddenRows.Count > 0 Then
                        For Each row1 In listOfHiddenRows
                            row1.Hidden = True
                        Next
                    End If
                    ExpToExcel.Export(grdProjects, lobjws, 0, 0)
                    If listOfHiddenColumns.Count > 0 Then
                        For Each column In listOfHiddenColumns
                            column.Hidden = True
                        Next
                    End If
                End If
                ExcWorkbook.Save(name)
                lblMessage.Visible = False
                pnlCBContainer.Enabled = True
                pnlButton.Enabled = True
                ShowMessageInfo(MessageManager.GetMessage("EXCELFILECREATED"))
                Try
                    excel = New Microsoft.Office.Interop.Excel.Application
                    wb = excel.Workbooks.Open(name, , False)
                    excel.Visible = True
                    'wb.Activate()d
                Catch ex As COMException
                    MessageBox.Show("Error accessing Excel: " + ex.ToString())

                Catch ex As Exception
                    MessageBox.Show("Error: " + ex.ToString())
                End Try
            End If
        Catch ex As Exception
            ShowMessageInfo(ex.Message)
            Logmessage.Append("There is error in Export of form Project management.")
            Logmessage.Append(Environment.NewLine)
            Logmessage.Append("Error Message - ")
            Logmessage.Append(ex.ToString())
            Me.LogErrorToDatabase(Logmessage.ToString)
        Finally
            pnlButton.Enabled = True
            fileName = String.Empty
            dialog = Nothing
            showDialog = Nothing
            name = Nothing
            ExpToExcel = Nothing
            Logmessage = Nothing
            listOfHiddenColumns = Nothing
        End Try
    End Sub

    Private Sub grdProjects_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles grdProjects.Click

    End Sub

    Private Sub grdProjects_InitializeRow(ByVal sender As Object, ByVal e As Infragistics.Win.UltraWinGrid.InitializeRowEventArgs) Handles grdProjects.InitializeRow
        'e.Row.Cells(0).Value = 
    End Sub

    Private Sub UltraGridExcelExporter1_ExportStarted(ByVal sender As System.Object, ByVal e As Infragistics.Win.UltraWinGrid.ExcelExport.ExportStartedEventArgs) Handles UltraGridExcelExporter1.ExportStarted
        For Each row As UltraGridRow In grdProjects.Rows
            row.Hidden = False
        Next

    End Sub

    Private Sub btnExeSummary_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnExeSummary.Click
        MessageBox.Show("Under development.")
    End Sub
End Class
Class CustomMergedCellEvaluators
    Implements Infragistics.Win.UltraWinGrid.IMergedCellEvaluator

    Function ShouldCellsBeMerged(ByVal row1 As UltraGridRow, ByVal row2 As UltraGridRow, ByVal column As UltraGridColumn) As Boolean Implements IMergedCellEvaluator.ShouldCellsBeMerged
        Dim name1 As String = DirectCast(row1.GetCellValue(column).ToString, String)
        Dim name2 As String = DirectCast(row2.GetCellValue(column).ToString, String)
        If column.Key = "Client Name" Then
            If name1 = name2 Then
                If row1.Index = 0 Then
                    row1.Appearance.BackColor = Color.White
                    row2.Appearance.BackColor = Color.White
                Else
                    If row1.Appearance.BackColor = Color.White Then
                        row2.Appearance.BackColor = Color.White
                    Else
                        row2.Appearance.BackColor = Color.FromArgb(241, 245, 250)
                    End If
                End If
                'row2.Hidden = True
            Else
                If row1.Appearance.BackColor = Color.White Then
                    row2.Appearance.BackColor = Color.FromArgb(241, 245, 250)
                Else
                    row2.Appearance.BackColor = Color.White
                End If
            End If
            Return name1 = name2
        ElseIf column.Key = "Listing" Then
            Return False
        ElseIf column.Key = "" Then
            Return False
        ElseIf column.Key = "Ratio" Then
            Return False
        ElseIf column.Key = "ISIN" Then
            Return False
        ElseIf column.Key = "CUSIP" Then
            Return False
        ElseIf column.Key = "SDD" Then
            If row2.Cells(1).Value.ToString <> row1.Cells(1).Value.ToString Then
                Return False
            End If
            Return name1 = name2
        ElseIf column.Key = "FDRD" Then
            If row2.Cells(0).Value.ToString <> row1.Cells(0).Value.ToString Then
                Return False
            End If
            Return (name1 = name2)
        ElseIf column.Key = "Project Status" Then
            If row2.Cells(0).Value.ToString <> row1.Cells(0).Value.ToString Then
                Return False
            End If
            Return name1 = name2

        ElseIf column.Key = "Completed Date" Then
            If row2.Cells(0).Value.ToString <> row1.Cells(0).Value.ToString Then
                Return False
            End If
            Return name1 = name2

        ElseIf column.Key = "Ignored" Then
            If row2.Cells(0).Value.ToString <> row1.Cells(0).Value.ToString Then
                Return False
            End If
            Return name1 = name2

        ElseIf column.Key = "Shares in Issue" Then
            If row2.Cells(0).Value.ToString <> row1.Cells(0).Value.ToString Then
                Return False
            End If
            Return name1 = name2

        ElseIf column.Key = "Shares Outstanding" Then
            If row2.Cells(0).Value.ToString <> row1.Cells(0).Value.ToString Then
                Return False
            End If
            Return name1 = name2

        ElseIf column.Key = "Free - Float" Then
            If row2.Cells(0).Value.ToString <> row1.Cells(0).Value.ToString Then
                Return False
            End If
            Return name1 = name2

        ElseIf column.Key = "Country" Then
            If row2.Cells(0).Value.ToString <> row1.Cells(0).Value.ToString Then
                Return False
            End If
            Return name1 = name2

        ElseIf column.Key = "Region" Then
            If row2.Cells(0).Value.ToString <> row1.Cells(0).Value.ToString Then
                Return False
            End If
            Return name1 = name2

        ElseIf column.Key = "Report History" Then
            If row2.Cells(0).Value.ToString <> row1.Cells(0).Value.ToString Then
                Return False
            End If
            Return name1 = name2

        ElseIf column.Key = "DD Form" Then
            If row2.Cells(0).Value.ToString <> row1.Cells(0).Value.ToString Then
                Return False
            End If
            Return name1 = name2

        ElseIf column.Key = "DTC List" Then
            If row2.Cells(0).Value.ToString <> row1.Cells(0).Value.ToString Then
                Return False
            End If
            Return name1 = name2

        ElseIf column.Key = "Share Register" Then
            If row2.Cells(0).Value.ToString <> row1.Cells(0).Value.ToString Then
                Return False
            End If
            Return name1 = name2

        ElseIf column.Key = "Offerings List" Then
            If row2.Cells(0).Value.ToString <> row1.Cells(0).Value.ToString Then
                Return False
            End If
            Return name1 = name2
        End If
        Return False
    End Function
End Class